package com.tis.database.dao.promis.impl;

import java.util.Collections;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import promis.utility.CommonService;

import com.tis.database.dao.ClsInstance;
import com.tis.database.dao.impl.AbstractDaoImpl;
import com.tis.database.dao.promis.VwSyncCustomerDao;
import com.tis.database.entitys.CustomerNotSend;
import com.tis.database.entitys.promis.VwSyncCustomer;

@Repository
@SuppressWarnings("unchecked")
public class VwSyncCustomerDaoImpl extends AbstractDaoImpl<VwSyncCustomer, String> implements VwSyncCustomerDao {
	private static final Logger logging = LoggerFactory.getLogger(VwSyncCustomerDaoImpl.class);
	
	protected VwSyncCustomerDaoImpl() {
		super(VwSyncCustomer.class);
	}
	
	@Override
	public List<CustomerNotSend> SearchByIsNotSend(int maxResult)  throws Exception {
		try{
			Session session = getCurrentSession();
			StringBuilder sql = new StringBuilder();
			
			sql.append(" SELECT CUSTOMER_CODE,CREATED_BY, CREATED_ON, LAST_UPD_BY, LAST_UPD_ON, SYNC_ISSYNC, SYNC_ISSTAGING FROM "+ ClsInstance.PROMIS_SCHEMA + ClsInstance.POINTER +"CUSTOMERS ");
			sql.append(" WHERE 1 = 1   ");
			sql.append(" AND ((NVL(SYNC_ISSTAGING,'N') = 'N') OR (NVL(SYNC_ISSYNC,'N') = 'N') ) "); 
			sql.append(" GROUP BY CUSTOMER_CODE,CREATED_BY, CREATED_ON, LAST_UPD_BY, LAST_UPD_ON, SYNC_ISSYNC, SYNC_ISSTAGING ");
			sql.append(" ORDER BY CUSTOMER_CODE DESC, CREATED_BY DESC , CREATED_ON DESC , LAST_UPD_BY DESC , LAST_UPD_ON DESC, SYNC_ISSYNC DESC, SYNC_ISSTAGING DESC ");
			
			Query q = session.createSQLQuery(sql.toString());
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);	
			
			List lst = q.list();
			logging.info("Size=" + CommonService.getSize(lst));
			
			return Collections.checkedList(lst, CustomerNotSend.class);
		}catch(Exception e){
			e.printStackTrace();
			logging.error(e.getMessage());
			throw e;
		}
	}

	@Override
	public List<VwSyncCustomer> Search(	String customerCode, 
										String idNo,
										String taxId, 
										String instanceNo, 
										String createdOnStart,
										String createdOnEnd, 
										int maxResult) throws HibernateException, Exception {
		try {
			
			Session session = getCurrentSession();
			Query q = session.getNamedQuery(VwSyncCustomer.findBySearch);
			String query = q.getQueryString();
			
			if(!CommonService.IsNull(customerCode))
				query += " And upper(c.customerCode) like '%"+ CommonService.IsNullRString(customerCode).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(idNo))
				query += " And upper(c.idNo) like '%"+ CommonService.IsNullRString(idNo).toUpperCase() +"%' ";
			
			if(!CommonService.IsNull(taxId))
				query += " And upper(c.taxId) like '%"+ CommonService.IsNullRString(taxId).toUpperCase() +"%'";
			
			if(!CommonService.IsNull(createdOnStart) && !CommonService.IsNull(createdOnEnd))
				query += " And to_char(c.createdOn, 'YYYY-MM-DD') BETWEEN :createdOnStart And :createdOnEnd ";
			
			query += " Order by :p_orderby "; 	
			
			logging.info(query);
			
			q = session.createQuery(query);
			
			if(!CommonService.IsNull(createdOnStart))
				q.setParameter("createdOnStart", CommonService.IsNullRString(createdOnStart).toUpperCase());
			
			if(!CommonService.IsNull(createdOnEnd))
				q.setParameter("createdOnEnd", CommonService.IsNullRString(createdOnEnd).toUpperCase());
			
			q.setParameter("p_orderby", "ACS");
			
			if(maxResult > 0)
				q.setMaxResults(maxResult);			
			
			List<VwSyncCustomer> lst = Collections.checkedList(q.list(), VwSyncCustomer.class);
			if(CommonService.getSize(lst) > 0 )
				return lst;
			else 
				return null;
			
		}catch(HibernateException ex) {
			logging.error(ex.getMessage());
			ex.printStackTrace();
			throw ex;
		}
		catch(Exception ex1) {
			logging.error(ex1.getMessage());
			ex1.printStackTrace();
			throw ex1;
		}
	}

}
